package ro.fortech.peaa.repository.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;

import ro.fortech.peaa.domain.Job;
import ro.fortech.peaa.repository.api.IJobMapper;

/**
 * JDBC implementation of the Job Mapper interface
 * @author robert
 *
 */
public class JdbcJobMapper extends SimpleJdbcDaoSupport implements IJobMapper {

	/** Logger for this class and subclasses */
	protected final Log logger = LogFactory.getLog(getClass());

	/*
	 * (non-Javadoc)
	 * @see ro.fortech.peaa.repository.api.IJobMapper#delete(ro.fortech.peaa.domain.Job)
	 */
	@Override
	public void delete(Job job) {
		String query = "DELETE FROM " + IJobMapper.TABLE + " WHERE " + IJobMapper.ID + "=:" + IJobMapper.ID;
		logger.info("Executing query: " + query);
		int count = getSimpleJdbcTemplate().update(query, new MapSqlParameterSource().addValue(IJobMapper.ID, job.getId()));
		logger.info("Rows affected: " + count);
	}

	/*
	 * (non-Javadoc)
	 * @see ro.fortech.peaa.repository.api.IJobMapper#find(java.lang.String)
	 */
	@Override
	public Job find(String id) {
		logger.info("Get a jobs with all the attributes set");
		String query = "SELECT " 
			+ IJobMapper.ID + ", " 
			+ IJobMapper.TITLE + ", " 
			+ IJobMapper.MIN_SALARY + ", " 
			+ IJobMapper.MAX_SALARY 
			+ " FROM " + IJobMapper.TABLE
			+ "WHERE " + IJobMapper.ID + "=:" + IJobMapper.ID;
		logger.info("Excuting query: " + query);
		List<Job> jobs = getSimpleJdbcTemplate().query(query, new JobAttrMapper());
		if (jobs != null && jobs.size() == 1) {
			return jobs.get(0);
		}
		return null;
	}

	/*
	 * (non-Javadoc)
	 * @see ro.fortech.peaa.repository.api.IJobMapper#findAll()
	 */
	@Override
	public List<Job> findAll() {
		logger.info("Get all jobs");
		String query = "SELECT " 
			+ IJobMapper.ID + ", " 
			+ IJobMapper.TITLE + ", " 
			+ IJobMapper.MIN_SALARY + ", " 
			+ IJobMapper.MAX_SALARY 
			+ " FROM " + IJobMapper.TABLE;
		logger.info("Excuting query: " + query);
		List<Job> jobs = getSimpleJdbcTemplate().query(query, new JobAttrMapper());		
		return jobs;
	}

	/*
	 * (non-Javadoc)
	 * @see ro.fortech.peaa.repository.api.IJobMapper#findAllIdAndTitle()
	 */
	@Override
	public List<Job> findAllIdAndTitle() {
		logger.info("Get all jobs, but only with id and title attributes set");
		String query = "SELECT " 
			+ IJobMapper.ID + ", " 
			+ IJobMapper.TITLE + " FROM " 
			+ IJobMapper.TABLE;
		logger.info("Excuting query: " + query);
		List<Job> jobs = getSimpleJdbcTemplate().query(query, new JobAttrMapper(true));		
		return jobs;
	}


	/*
	 * (non-Javadoc)
	 * @see ro.fortech.peaa.repository.api.IJobMapper#insert(ro.fortech.peaa.domain.Job)
	 */
	@Override
	public void insert(Job job) {
		// TODO Auto-generated method stub

	}

	/*
	 * (non-Javadoc)
	 * @see ro.fortech.peaa.repository.api.IJobMapper#update(ro.fortech.peaa.domain.Job)
	 */
	@Override
	public void update(Job job) {
		logger.info("updating job: " + job);
		int count = getSimpleJdbcTemplate().update(
				"UPDATE " + IJobMapper.TABLE + " SET " 
				+ IJobMapper.ID + "=:" + IJobMapper.ID + ", " 
				+ IJobMapper.TITLE + "=:" + IJobMapper.TITLE + ", " 
				+ IJobMapper.MIN_SALARY + "=:" + IJobMapper.MIN_SALARY + ", " 
				+ IJobMapper.MAX_SALARY + "=:" + IJobMapper.MAX_SALARY 
				+ " WHERE " + IJobMapper.ID + "=:" + IJobMapper.ID + "",
				new MapSqlParameterSource().addValue(" + JobMapper.ID + ", job.getId())
				.addValue(IJobMapper.TITLE, job.getTitle())
				.addValue(IJobMapper.MIN_SALARY, job.getMinSalary())
				.addValue(IJobMapper.MAX_SALARY, job.getMaxSalary()));
		logger.info("Rows affected: " + count);
	}

	/**
	 * Maps the attributes of a Job object to a row in the table.
	 * @author robert
	 *
	 */
	private static class JobAttrMapper implements ParameterizedRowMapper<Job> {
		
		/**
		 * This flag indicates if only the id and title fields should be mapped 
		 */
		private boolean onlyIdAndTitle;
		
		public JobAttrMapper() {
			super();
			onlyIdAndTitle = false;
		}
		
		public JobAttrMapper(boolean onlyIdAndTitle) {
			super();
			this.onlyIdAndTitle = onlyIdAndTitle;
		}

		public Job mapRow(ResultSet rs, int arg1) throws SQLException {
			Job job = new Job();
			job.setId(rs.getString(IJobMapper.ID));
			job.setTitle(rs.getString(IJobMapper.TITLE));
			if (!onlyIdAndTitle) {
				job.setMinSalary(rs.getInt(IJobMapper.MIN_SALARY));
				job.setMaxSalary(rs.getInt(IJobMapper.MAX_SALARY));
			}
			return job;
		}
	}
}
